With 2k5 I am trying to make the "Top N" operator, used in a chart "category," return only N categories. But it almost always returns more than N categories. Can someone explain the nuances of the Top N operator, so I can get past this?

Is N fixed number, If yes then you can try filtering on rownumber. One more option is to create a different dataset and then use "select top 10" syntax in query itself. HTH1 Priyank

Whether N is fixed or not, "rownumber" does not correlate to chart "category" grouping. I want the top 10 categories, not the top ten rows. I don't want to use "select top N" in my data set, because it would require refactoring my query to use a "group by" expression. What I want is for the grouping to be handled by the chart. So this brings me back to the beginning. Can someone explain the highly erratic behavior of the "top N" operator? It sometimes returns more than N, sometimes less, and it often screws up the sort order. Is this something fixed in a SSRS patch?

Ok, I figured it out. First of all, assuming the perspective of "order ascending," "Top N" actually means "Bottom N," and "Bottom N" actually means "Top N." Go figure. Also, the "Top N" operator, at least when used with a chart "category" filter, does the following: 1) Uses the category field expression to select the first row of each category 2) Sorts the selection of "first rows" that came from the category, based on the field expression that was given to the "Top N" operator itself 3) Performs a dense_rank() function on that output 4) Selects the "Top N" of the dense rank 5) Afterward, the "Sorting" spec of the category is applied This is why I sometimes get more than "N" categories returned.

For my experience, the "Top N" has some interesting behaviors: 1. If there are ties in count, the result contains more than N categories. 2. Top N only considers ties at the end of sequence. Let's look at two examples: Suppose we want Top 3 First example: 1, 2, 3, 3, 4, 5 return 1, 2, 3, 3 Second example: 1, 2, 2, 3, 3, 4, 5 return 1, 2,2 Hope these examples could help

Thanks for the infor Mr L - very useful to know. Can you tell me is there a way to exclude a tied figure from the end of a sequence? I have a top 10 table which includes a total of all the values - if i get and extra 11th value this total is obviously going to nr ouut of whack,. Any ideas? Many thanks P